package multiplayerlogic;

/**************************
 * Author: Anders Hansson *
 **************************/

import java.sql.*;
import java.util.ArrayList;

public class DatabaseConnecter {

    private String driver = "net.sourceforge.jtds.jdbc.Driver";
    private String url = "jdbc:jtds:sqlserver://idasql-db.hb.se:56001/dbtht1103";
    private String userName = "dbtht1103";
    private String password = "wagga09";
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private int wins, draws, losses, score;
    private ArrayList list;

    // Establishes the connection to the database.
    public DatabaseConnecter() throws ClassNotFoundException, SQLException {
        try {

            // Load the driver.
            Class.forName(driver);
            System.out.println("Driver loaded.");

            // Establish connection.
            connection = DriverManager.getConnection(url, userName, password);
            System.out.println("Database connected");

        } catch (Exception e) {
            System.out.println("Error while connecting to database: " + e);
        }
    }

    // Checks if the user is has inserted the correct username and password
    // by matching them to the databse, returns true if both are correct and
    // false if one of them is not matching.
    public boolean loginStatement(String name, String password) {
        try {

            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM Users WHERE username='" + name
                    + "' AND userpassword='" + password + "'");
            if (resultSet.next() == false) {
                return false;
            }

        } catch (SQLException ex) {
            System.out.println("Error in LOGGING IN.");
        }
        return true;
    }

    // Registers a new user in the database with the username and a password.
    public boolean createNewUserStatement(String name, String password) {
        try {

            statement = connection.createStatement();
            statement.executeUpdate("INSERT INTO Users (username, userpassword)"
                    + "VALUES ('" + name + "','" + password + "')");

        } catch (SQLException ex) {
            System.out.println("Error in creating NEW USER.");
        }
        return true;
    }

    // Checks if the given username exists in the database.
    public boolean checkUsername(String name) {
        try {

            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM Users WHERE username ='" + name + "'");

            if (resultSet.next() == false) {
                return false;
            }

        } catch (SQLException e) {
            System.out.println("Error in retrieving USERNAME.");
        }
        return true;
    }

    // Retrieves a copy of the the best players highscores from the database, inserts them
    // into a arraylist and then returns that list.
    public ArrayList selectHighScoreStatement() {
        String query = "";
        try {

            query = "SELECT TOP 25 * FROM (SELECT p1name as pname, p1score as pscore FROM Highscore "
                    + "UNION SELECT p2name as pname, p2score as pscore FROM Highscore)"
                    + " AS topper ORDER BY pscore DESC";
            
            statement = connection.createStatement();
            resultSet = statement.executeQuery(query);

            list = new ArrayList();

            while (resultSet.next()) {
                list.add(resultSet.getString(1));
                list.add(resultSet.getInt(2));
            }

        } catch (SQLException e) {
            System.out.println("Error in retrieving HIGHSCORE: " + query);
        }
        return list;
    }

    // Insert a whole finnished game into the highscore database with sessionID (current date when game was finnished),
    // both players usernames and their scores.
    public void updateHighScoreStatement(String session, String name1, String name2, int p1points, int p2points) {
        try {

            statement = connection.createStatement();
            statement.executeUpdate("INSERT INTO Highscore (sessionID, p1name, p2name, p1score, p2score) VALUES ('"
                    + session + "', '" + name1 + "', '" + name2 + "', '" + p1points + "', '" + p2points + "')");

        } catch (SQLException e) {
            System.out.println("Error in inserting HIGHSCORE.");
        }
    }

    // Updates or Inserts a players ranking to a ranking database, If its the players first
    // game it will insert his name and result from that game into the ranking, if its a 
    // player that has already a post in the ranking database it will update/add the 
    // stats for that player. The ranking database stores the username, number of wins, draws
    // and the number of lost games and with those stats it will calculate a score to order
    // the ranking among the players, a win gives 25 points, draw gives 10 and a loss gives
    // -5 points.
    public void updateRankingStatement(String name, int win, int draw, int loss) {
        try {

            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM Ranking WHERE username ='" + name + "'");

            System.out.println("INSERT INTO Ranking (username, wins, draws, losses, score) VALUES ('"
                    + name + "', '" + win + "', '" + draw + "', '" + loss
                    + "', '" + score + "')");

            if (resultSet.next() == false) {

                score = 25 * win + 10 * draw + loss * -5;

                statement = connection.createStatement();
                statement.executeUpdate("INSERT INTO Ranking (username, wins, draws, losses, score) VALUES ('"
                        + name + "', '" + win + "', '" + draw + "', '" + loss
                        + "', '" + score + "')");
            } else {

                wins = resultSet.getInt(2);
                draws = resultSet.getInt(3);
                losses = resultSet.getInt(4);

                wins += win;
                draws += draw;
                losses += loss;

                score = 25 * wins + 10 * draws + losses * -5;

                statement = connection.createStatement();
                statement.executeUpdate("UPDATE Ranking SET wins='" + wins + "', draws='" + draws + "', losses='"
                        + losses + "', score='" + score + "' WHERE username='" + name + "'");
                
            }
            
        } catch (Exception e) {
            System.out.println("Error in updating RANKING.");
        }
    }

    // Retrieves a copy of the the current ranking among the players from the 
    // ranking database, inserts them into a arraylist and then returns that list.
    public ArrayList selectRankingStatement() {
        try {
            
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT TOP 25 * FROM Ranking ORDER BY score DESC");

            list = new ArrayList();

            while (resultSet.next()) {            
                list.add(resultSet.getString(1));
                list.add(resultSet.getInt(2));
                list.add(resultSet.getInt(3));
                list.add(resultSet.getInt(4));
                list.add(resultSet.getInt(5));              
            }
            
        } catch (Exception e) {
            System.out.println("Error in retrieving RANKING.");
        }
        return list;
    }
}
